﻿
/****** Object:  Stored Procedure dbo.spDataDocument    Script Date: 10/14/2009 10:39:05 PM ******/

CREATE procedure spDataDocument as

--temp table to store results
if exists ( select 1 from sysobjects where name = 'tmpDataDocument' )
	drop table tmpDataDocument
create table tmpDataDocument ( TableName varchar(100), TableDescription varchar(255), ColumnName varchar(100), ColumnDescription varchar(255))

set nocount on 

declare c1 cursor for 
	select name, id from sysobjects where xtype = 'U' order by name

open c1
	declare @TableName varchar(255)
	declare @TableDescription varchar(255)
	declare @ColumnName varchar(255)
	declare @ColumnDescription varchar(255)
	declare @TableId int

	declare @name2 varchar(255)
	declare @value2 sql_variant
	declare @value varchar(4000)


fetch next from c1 into @TableName, @TableId

while @@FETCH_STATUS = 0 
begin
	set @ColumnName = null
	select @value2=value from ::fn_listextendedproperty
		( NULL, 'USER', 'dbo', 'TABLE', @TableName, NULL, NULL ) as tablename
	if @value2 is not null 
	begin

		set @TableDescription = cast( @value2 as varchar(4000) )
	
		declare c2 cursor for 
			select name from syscolumns where id = @TableId
		open c2

		fetch next from c2 into @ColumnName

		while @@FETCH_STATUS = 0 
		begin
			set @value2 = null
			SELECT  @value2=value FROM ::fn_listextendedproperty (NULL, 'USER', 'dbo', 'TABLE',@TableName, 'column', @ColumnName) as columnname
			if @value2 is not null
				set @ColumnDescription = cast( @value2 as varchar(4000) )
			else
				set @ColumnDescription = 0
			-- save data to table 
			insert into tmpDataDocument ( TableName, TableDescription, ColumnName, ColumnDescription)
				values (@TableName, @TableDescription, @ColumnName, @ColumnDescription)

		fetch next from c2 into @ColumnName
		end
		close c2
		deallocate c2
	end
	
	fetch next from c1 into @TableName, @TableId
end
close c1
deallocate c1
select * from tmpDataDocument
